/**
   Creator : Thinh Nguyen
   Created Date: 23/2/2013
   Modified Date: 24/2/2013
**/

CREATE DATABASE ExpressBookingSystem_THPN

USE ExpressBookingSystem_THPN

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserProfile]') AND
		TYPE IN (N'U'))
		DROP TABLE [dbo].[UserProfile]
/** Create User profile **/
CREATE TABLE [UserProfile]
(
	UserProfileId INT NOT NULL PRIMARY KEY,
	FirstName NVARCHAR(50) NOT NULL,
	LastName NVARCHAR(50) NOT NULL,
	PhoneNumber VARCHAR(50) NOT NULL,
	Email NVARCHAR(128) NOT NULL,
	[Address] NVARCHAR(128) NULL,
	City NVARCHAR(50) NULL,
	[State] NVARCHAR(50) NULL,
	Country NVARCHAR(50) NULL,
	Deleted BIT NOT NULL
)
CREATE INDEX idxUserProfile ON dbo.UserProfile (FirstName, LastName)

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserRole]') AND
		TYPE IN (N'U'))
		DROP TABLE [dbo].[UserRole]
/** Create UserRole table **/
CREATE TABLE [UserRole]
(
	RoleId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	RoleName NVARCHAR(256) NOT NULL,
	[Description] NVARCHAR(256) NOT NULL,
	Deleted BIT NOT NULL
)

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[User]') AND
		TYPE IN (N'U'))
		DROP TABLE [dbo].[User]
/** Create User table **/
CREATE TABLE [User]
(
	UserId INT NOT NULL PRIMARY KEY,
	Username VARCHAR(50) NOT NULL,
	[Password] NVARCHAR(128) NOT NULL,
	PasswordQuestion NVARCHAR(256) NULL,
	PassswordAnswer NVARCHAR(128) NULL,
	IsApproved BIT NOT NULL,
	IsLocked BIT NOT NULL,
	CreatedDate DateTime NOT NULL,
	LastLoginDate Datetime NOT NULL,
	LastPasswordChangedDate Datetime NOT NULL,
	Deleted BIT NOT NULL,
	UserProfileId INT FOREIGN KEY REFERENCES [dbo].[UserProfile](UserProfileId),
	RoleId INT FOREIGN KEY REFERENCES [dbo].[UserRole](RoleId)
)
CREATE INDEX idxUser ON [dbo].[User] (Username)

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BusPort]') AND
		TYPE IN (N'U'))
		DROP TABLE [dbo].[BusPort]
/** Create bus port table **/
CREATE TABLE [BusPort]
(
	BusPortId INT NOT NULL PRIMARY KEY,
	Name NVARCHAR(128) NOT NULL,
	Location NVARCHAR(128) NOT NULL,
	Deleted BIT NOT NULL
)

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BusType]') AND
		TYPE IN (N'U'))
		DROP TABLE [dbo].[BusType]
/** Create BusType table **/
CREATE TABLE [BusType]
(
	BusTypeId INT NOT NULL PRIMARY KEY,
	Name NVARCHAR(128) NOT NULL,
	Deleted BIT NOT NULL
)

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TripShedule]') AND
		TYPE IN (N'U'))
		DROP TABLE [dbo].[TripShedule]
/** Create TripSchedule table **/
CREATE TABLE [TripShedule]
(
	TripScheduleId INT NOT NULL PRIMARY KEY,
	DepartureDate DATETIME NOT NULL,
	ArrivalDate DATETIME NOT NULL,
	DeparturePortId INT FOREIGN KEY REFERENCES [dbo].[BusPort](BusPortId),
	ArrivalPortId INT FOREIGN KEY REFERENCES [dbo].[BusPort](BusPortId),
	BusTypeId INT FOREIGN KEY REFERENCES [dbo].[BusType](BusTypeId),
	Price FLOAT NOT NULL,
	Deleted BIT NOT NULL
)

CREATE INDEX idxDateTripSchedule ON [dbo].[TripShedule]  (DepartureDate, ArrivalDate)
CREATE INDEX idxBusPort ON [dbo].[TripShedule]  (DeparturePortId, ArrivalPortId)

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Booking]') AND
		TYPE IN (N'U'))
		DROP TABLE [dbo].[Booking]
/** Create Booking table **/
CREATE TABLE [Booking]
(
	BookingId INT NOT NULL PRIMARY KEY,
	UserId INT FOREIGN KEY REFERENCES [dbo].[User],
	BookedDate DATETIME NOT NULL,
	PaymentRecieved BIT NOT NULL,
	ConfirmedDate DATETIME NOT NULL,
	Total FLOAT NOT NULL,
	Deleted BIT NOT NULL
)

CREATE INDEX idxUserBooked ON [dbo].[Booking]  (UserId)
CREATE INDEX idxBookedDate ON [dbo].[Booking]  (BookedDate)

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BookingDetail]') AND
		TYPE IN (N'U'))
		DROP TABLE [dbo].[BookingDetail]
/** Create BookingDetail table **/
CREATE TABLE [BookingDetail]
(
	BookingDetailId INT NOT NULL PRIMARY KEY,
	CreatedDate DATETIME NOT NULL,
	Quantity INT NOT NULL,
	TripID INT FOREIGN KEY REFERENCES [dbo].[TripShedule],
	BookingId INT FOREIGN KEY REFERENCES [dbo].[Booking],
	IsBooked BIT NOT NULL,
	TotalPrice FLOAT NOT NULL,
	Deleted BIT NOT NULL
)

CREATE INDEX idxBooking ON [dbo].[BookingDetail] (BookingId)

